package iss.java.part2;

import com.sun.tools.javac.util.Convert;
import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;

import java.io.File;
import java.io.FileOutputStream;
import java.util.Iterator;

/**
 * Created by WeiZehao on 16/10/17.
 */

public class OperateExcel
{
    private POIFSFileSystem _fs;
    private HSSFWorkbook _workbook; // excel文件
    private String _inputPath;      // 输入文件的路径
    private HSSFCellStyle _style;   // 单元格样式

    public OperateExcel(String inputPath)
    {
        _inputPath = inputPath;
    }

    /*获得Excel文件*/
    public void getExcel()
    {
        File file = new File(_inputPath);
        try
        {
            _fs = new POIFSFileSystem(file);
            _workbook = new HSSFWorkbook(_fs);
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }
    }// end method

    /*输出Excel文件*/
    public void outputExcel()
    {
        try
        {
            FileOutputStream outputStream = new FileOutputStream("document/outputExcel.xls");
            _workbook.write(outputStream);
            outputStream.close();
        }
        catch (Exception e)
        {
            e.printStackTrace();
        }

    }// end method

    /*目标一：读取单元格内容*/
    public void readExcelCell()
    {
        HSSFSheet sheet = _workbook.getSheetAt(0);
        // 遍历所有单元格
        for(Iterator<Row> it = sheet.rowIterator(); it.hasNext();)
        {
            // 获得行
            Row row = it.next();
            for(Iterator<Cell> it2 = row.cellIterator(); it2.hasNext();)
            {
                // 获得单元格
                Cell cell = it2.next();
                System.out.print(cell + " ");
            }
            System.out.println();
        }
    }// end method

    /*目标二：设置单元格格式测试，将表格学号一栏格式设置为数值，不保留小数位*/
    public void setCellFormat()
    {
        // 生成一种样式
        _style = _workbook.createCellStyle();
        _style.setDataFormat(HSSFDataFormat.getBuiltinFormat("0"));

        HSSFSheet sheet = _workbook.getSheetAt(0);
        // 设置第二列单元格宽度15个字符
        sheet.setColumnWidth(1, 15 * 256);
        // 行迭代器
        Iterator<Row> it = sheet.rowIterator();

        // 跳过第一行
        it.next();
        // 从第二行开始遍历行
        while (it.hasNext())
        {
            Row row = it.next();
            // 获得第二列的单元格
            Cell cell = row.getCell(1);
            // 为单元格设置样式
            cell.setCellStyle(_style);
        }
    }// end method

    /*目标三：合并单元格，合并最后一列和其后的空列*/
    public void mergeCells()
    {
        HSSFSheet sheet = _workbook.getSheetAt(0);
        // 声明合并区域
        CellRangeAddress region;

        // 合并每一行第8、9两列的单元格
        for(int i = 0; i <= 39; i++)
        {
            region = new CellRangeAddress(i,i,7,8);
            sheet.addMergedRegion(region);
        }
    }// end method

    /*目标四：单元格内容居中对齐*/
    public void changeAlign()
    {
        // 设置生成水平、垂直居中对齐样式
        _style.setAlignment(HorizontalAlignment.CENTER);
        _style.setVerticalAlignment(VerticalAlignment.CENTER);

        HSSFSheet sheet = _workbook.getSheetAt(0);
        // 遍历所有单元格并设置对齐样式
        for(Iterator<Row> it = sheet.rowIterator(); it.hasNext();)
        {
            // 获得行
            Row row = it.next();
            for(Iterator<Cell> it2 = row.cellIterator(); it2.hasNext();)
            {
                // 获得单元格
                Cell cell = it2.next();
                // 设置单元格样式
                cell.setCellStyle(_style);
            }
        }
    }// end method

    /*目标五：为单元格设置公式，计算总成绩*/
    public void setSumFormula()
    {
        HSSFSheet sheet = _workbook.getSheetAt(0);
        // 生成行迭代器
        Iterator<Row> it = sheet.rowIterator();
        // 跳过第一行
        it.next();

        // 用于生成表示公式的字符串
        String s1 = "sum(C";
        String s2 = ":G";
        String s3 = ")";

        for(int i = 2; it.hasNext(); i++)
        {
            Row row = it.next();
            // 获取到总成绩一列的单元格
            Cell cell = row.createCell(7);

            // 生成 sum(C2:G2) 样式的公式
            String si = Integer.toString(i);
            String formula = s1 + si + s2 + si + s3;
            // 为单元格设置公式
            cell.setCellFormula(formula);
        }
    }// end method
}
